﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using NovinMedia.Data;
using System.Data.SqlClient;

namespace QuickSQL
{
    public partial class SqlQueryForm : UserControl
    {
        public string DatabaseConnectionString = "";

        public SqlQueryForm()
        {
            InitializeComponent();
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(Client.Connection.ConnectionString))
            {
                Connect();
            }
            else
            {
                SqlOptions optionsWindow = new SqlOptions();
                if (optionsWindow.ShowDialog() == DialogResult.OK)
                {
                    DatabaseConnectionString = optionsWindow.SelectedConnectionString;
                    ClearClient();
                    Connect();
                }
            }
        }

        private void Connect()
        {
            try
            {
                Client.Connection.Open();

                cmbDatabases.Items.Clear();
                GetDatabasesList(Client.Connection).ForEach(db => cmbDatabases.Items.Add(db));
                cmbDatabases.SelectedItem = Client.Connection.Database;
                SetAsConnected();
            }
            catch(Exception ex)
            {
                SetasDisconnected();
                MessageBox.Show("Error: " + ex.Message);
            }


        }

        private void SetAsConnected()
        {
            btnConnect.Enabled = false;
            btnDisconnect.Enabled = true;
            cmbDatabases.Enabled = true;
            btnExecute.Enabled = true;
            txtQuery.Enabled = true;
            scMain.Panel2Collapsed = true;
            scMain.Panel2.Enabled = false;
        }

        private void SetasDisconnected()
        {
            btnConnect.Enabled = true;
            btnDisconnect.Enabled = false;
            cmbDatabases.Enabled = false;
            btnExecute.Enabled = false;
            txtQuery.Enabled = false;
            scMain.Panel2Collapsed = true;
            scMain.Panel2.Enabled = false;
        }

        private List<string> GetDatabasesList(System.Data.IDbConnection dbConnection)
        {
            System.Data.IDbCommand command = Client.CreateCommand("Select name from sys.sysdatabases", CommandType.Text, new List<IDbDataParameter>());
            command.Connection = dbConnection;
            List<string> databases = new List<string>();
            using (System.Data.IDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    databases.Add(reader.GetString(reader.GetOrdinal("name")));
                }
            }
            return databases;
        }
        private DbClient _client = null;
        public DbClient Client
        {
            get
            {
                if (_client == null)
                    _client = InstantiateClient();

                return _client;
            }
        }
        public void ClearClient()
        {
            _client.Dispose();
            _client = null;
        }

        private DbClient InstantiateClient()
        {
            return new SqlDbClient(DatabaseConnectionString);
        }

        private void btnExecute_Click(object sender, EventArgs e)
        {
            if (cmbDatabases.SelectedIndex < 0)
            {
                MessageBox.Show("A database must be selected");
            }
            else
            {
                try
                {

                    string query = txtQuery.SelectedText.Length>0 ? txtQuery.SelectedText : txtQuery.Text;

                    ExecuteQuery(query);

                }
                catch (Exception ex)
                {
                    txtResults.AppendText(string.Format("Error: {0})", ex.Message));

                    pnlGridResults.Visible = false;
                    pnlTextResults.Visible = true;
                }
                scMain.Panel2Collapsed = false;
                scMain.Panel2.Enabled = true;
            }
        }

        private void ExecuteQuery(string query)
        {
            grdResults.Columns.Clear();
            txtResults.Clear();
            System.Data.IDbCommand command = Client.CreateCommand(query, CommandType.Text, new List<IDbDataParameter>());
            command.Connection = Client.Connection;
            if (command.Connection.State == ConnectionState.Closed)
            {
                command.Connection.Open();
            }
            List<string> databases = new List<string>();
            command.Prepare();
            using (System.Data.IDataReader reader = command.ExecuteReader())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    grdResults.Columns.Add(new DataGridViewColumn(new DataGridViewTextBoxCell()) { Name = reader.GetName(i) });
                }
                while (reader.Read())
                {
                    List<object> listValues = new List<object>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        listValues.Add(reader.GetValue(i));
                    }
                    grdResults.Rows.Add(listValues.ToArray());
                }
                txtResults.AppendText(string.Format("\r\n({0} rows affected)", reader.RecordsAffected));
            }
            pnlGridResults.Visible = true;
            pnlTextResults.Visible = false;
        }

        private void scMain_SplitterMoved(object sender, SplitterEventArgs e)
        {

        }

        private void cmbDatabases_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbDatabases.SelectedIndex > -1)
            {
                Client.Connection.ChangeDatabase(cmbDatabases.SelectedItem as string);
            }
        }

        private void btnDisconnect_Click(object sender, EventArgs e)
        {
            if (Client.IsConnected())
            {
                ClearClient();
                DatabaseConnectionString = string.Empty;
            }
            SetasDisconnected();
        }

        private void SqlQueryForm_Load(object sender, EventArgs e)
        {
            if (this.ParentForm != null)
            {
                this.ParentForm.Text = "QuickSQL";
            }
            
            SetasDisconnected();
        }
    }
    public abstract class DbClient : IDisposable
    {
        private readonly string _connectionString = string.Empty;
        private IDbConnection _connection;

        public IDbConnection Connection
        {
            get
            {
                if (_connection == null)
                {
                    _connection = CreateConnection();
                }
                return _connection;
            }
        }

        public string ConnectionString
        {
            get { return _connectionString; }
        }


        public DbClient(string connectionString)
        {
            _connectionString = connectionString;
        }
        protected abstract IDbConnection CreateConnection();

        public virtual void Dispose()
        {
            if (_connection != null)
                _connection.Dispose();
        }


        public abstract IDbCommand CreateCommand(string text, CommandType commandType, List<IDbDataParameter> list);

        public bool IsConnected()
        {
            return Connection != null;
        }
    }

    public class SqlDbClient : DbClient
    {
        public SqlDbClient(string connectionString)
            : base(connectionString)
        {

        }
        protected override IDbConnection CreateConnection()
        {
            return new System.Data.SqlClient.SqlConnection(ConnectionString);
        }
        public override IDbCommand CreateCommand(string text, CommandType commandType, List<IDbDataParameter> list)
        {
            IDbCommand command = new System.Data.SqlClient.SqlCommand(text)
                {
                    CommandType = commandType
                };
            list.ForEach(parameter => command.Parameters.Add(parameter));
            return command;
        }
        public string GetConnectionString(string server, string database = null)
        {
            return string.Format("Data Source={0:G};Initial Catalog={1:G};Integrated Security=SSPI;", server, database);
        }
        public string GetConnectionString(string server, string username, string password, string database = null)
        {
            return string.Format("Server={0:G};Database={1:G};User ID={2:G};Password={3:G};Trusted_Connection=False;", server, database, username, password);
        }
    }
}
